package com.gxuwz.medical.dao;

import java.sql.*;
import java.util.*;

import com.gxuwz.medical.database.DbUtil;
import com.gxuwz.medical.domain.role.Role;
import com.gxuwz.medical.domain.user.User;
import com.gxuwz.medical.vo.PageBean;

public class UserDao extends GenericDao<User>{
	@Override
	protected User handle(ResultSet rs) throws SQLException {
		User u = new User();
		u.setUserid(rs.getString("userid"));
		u.setPwd(rs.getString("pwd"));
		u.setFullName(rs.getString("fullName"));
		
		//设置角色
		
		return u;
	}
	public User login(String userid, String pwd) throws SQLException{
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select * from t_user where userid='" + userid
					+ "' and pwd='" + pwd + "'";
			rs = db.executeQuery(stmt, sql);
			if (rs.next()) {
				return handle(rs);
			} else {
				return null;
			}

		} catch (SQLException e) {
			throw new SQLException("登录失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
	}
	public void add(User u) throws SQLException {
		try {
			DbUtil db = new DbUtil();
			String sql = "insert into t_user(userid,pwd,fullname) values('"
					+ u.getUserid() + "','" + u.getPwd() + "','"
					+ u.getFullName() + "')";
			db.executeUpdate(sql);

			Iterator<Role> it = u.getRoles().iterator();
			while (it.hasNext()) {
					sql = "insert into user_role(userid,roleid) values('"
							+ u.getUserid() + "','" + it.next().getRoleid() + "')";
					db.executeUpdate(sql);
			}
		} catch (SQLException e) {
			throw new SQLException("添加用户失败 " + e.getMessage(), e);
		}
	}

	public void delete(String userid) throws SQLException {
		try {
			DbUtil db = new DbUtil();
			String sql = "delete from t_user where userid='" + userid + "'";
			db.executeUpdate(sql);
		} catch (SQLException e) {
			throw new SQLException("删除用户失败" + e.getMessage(), e);
		}
	}

	public User query(String userid) throws SQLException {
		User u = new User();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select * from t_user where userid='" + userid
					+ "'";
			rs = db.executeQuery(stmt, sql);
			if (rs.next()) {
				u.setUserid(rs.getString("userid"));
				u.setPwd(rs.getString("pwd"));
				u.setFullName(rs.getString("fullName"));
			}
			sql = "select * from user_role where userid='" + userid
					+ "'";
			rs = db.executeQuery(stmt, sql);
			Set<Role> tmp = new HashSet();
			while(rs.next()){
				Role r=new RoleDao().query(rs.getString("roleid"));
				tmp.add(r);	
			}
			u.setRoles(tmp);
		} catch (SQLException e) {
			throw new SQLException("查询用户失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
		return u;
	}
	
	public int count() throws SQLException{//返回记录数
		int count = 0;
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		try {
			String sql = "select count(userid) from t_user";
			rs = stmt.executeQuery(sql);
			if(rs.next())
				count = rs.getInt(1);
		}catch (SQLException e) {
			throw new SQLException("查询记录数量失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
		return count;
	}

	
	public PageBean queryAll(int page,int size) throws SQLException {
		PageBean p = new PageBean(page,size,count());
		List ulist = new ArrayList();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		DbUtil db2 = new DbUtil();
		Connection conn2 = DbUtil.getConn();
		Statement stmt2 = conn2.createStatement();
		ResultSet rs2 = null;
		try {
			/*
			 * 查询用户列表时同时查询，用户所拥有的的角色。
			 * */
			String sql = "select * from t_user";
			rs = db.executeQuery(stmt, sql);
			while (rs.next()) {
				User user = new User();
				user.setUserid(rs.getString("userid"));
				user.setFullName(rs.getString("fullName"));
				/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
				String sql2="select c.roleid,c.roleName from t_user a,user_role b,t_role c where a.userid=b.userid and b.roleid=c.roleid and a.userid='"+rs.getString("userid")+"'";
				rs2 = db2.executeQuery(stmt2, sql2);
				while(rs2.next()){
					Role r = new Role();
					r.setRoleid(rs2.getString("roleid"));
					r.setRoleName(rs2.getString("roleName"));
					user.addRole(r);
				}
				/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
				ulist.add(user);
			}
			p.setPageData(ulist);
			return p;

		} catch (SQLException e) {
			throw new SQLException("查询用户失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
	}
	
	public List<User> queryAll() throws SQLException {
		List<User> ulist = new ArrayList<User>();
		DbUtil db = new DbUtil();
		Connection conn = DbUtil.getConn();
		Statement stmt = conn.createStatement();
		ResultSet rs = null;
		DbUtil db2 = new DbUtil();
		Connection conn2 = DbUtil.getConn();
		Statement stmt2 = conn2.createStatement();
		ResultSet rs2 = null;
		try {
			/*
			 * 查询用户列表时同时查询，用户所拥有的的角色。
			 * */
			String sql = "select * from t_user";
			rs = db.executeQuery(stmt, sql);
			while (rs.next()) {
				User user = new User();
				user.setUserid(rs.getString("userid"));
				user.setFullName(rs.getString("fullName"));
				/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
				String sql2="select c.roleid,c.roleName from t_user a,user_role b,t_role c where a.userid=b.userid and b.roleid=c.roleid and a.userid='"+rs.getString("userid")+"'";
				rs2 = db2.executeQuery(stmt2, sql2);
				while(rs2.next()){
					Role r = new Role();
					r.setRoleid(rs2.getString("roleid"));
					r.setRoleName(rs2.getString("roleName"));
					user.addRole(r);
				}
				/*----------------------------------------------------------------------------------------------------------------------------------------------------------------*/
				ulist.add(user);
			}
			
			return ulist;

		} catch (SQLException e) {
			throw new SQLException("查询用户失败" + e.getMessage(), e);
		}finally{
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
			if(conn!=null)
				conn.close();
		}
	}

	public void update(User u) throws SQLException {
		try {
			/*更新用户表*/
			DbUtil db = new DbUtil();
			String sql = "update t_user set pwd='" + u.getPwd() + "',fullName='"
					+ u.getFullName() + "' where userid='" + u.getUserid() + "'";
			db.executeUpdate(sql);
			/*更新角色*/
			sql = "delete from user_role where userid='"+u.getUserid()+"'";
			db.executeUpdate(sql);
			Iterator<Role> it = u.getRoles().iterator();
			while (it.hasNext()) {
					sql = "insert into user_role(userid,roleid) values('"
							+ u.getUserid() + "','" + it.next().getRoleid() + "')";
					db.executeUpdate(sql);
			}
			
			
		} catch (SQLException e) {
			throw new SQLException("修改用户失败 " + e.getMessage(), e);
		}
	}
	

}
